#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
根据实际表结构生成CRM测试数据
"""

import random
import datetime
from typing import List, Dict

# 配置数据
CUSTOMER_COUNT = 50

# 行业列表
INDUSTRIES = [
    "科技互联网", "金融保险", "教育培训", "医疗健康", "房地产建筑",
    "制造业", "电商零售", "文化传媒", "物流运输", "咨询服务",
    "餐饮酒店", "旅游度假", "法律服务", "会计审计", "政府机构"
]

# 企业规模
SCALES = [
    "1-10人", "11-50人", "51-200人", "201-500人", "501-1000人", "1000人以上"
]

# 客户来源
SOURCES = [
    "官网咨询", "电话营销", "朋友介绍", "展会获取", "网络推广",
    "老客户推荐", "线上广告", "陌生拜访", "行业合作", "社交媒体"
]

# 省市区数据
REGIONS = [
    ("北京市", "朝阳区", "三里屯"),
    ("北京市", "海淀区", "中关村"),
    ("上海市", "浦东新区", "陆家嘴"),
    ("上海市", "徐汇区", "徐家汇"),
    ("广州市", "天河区", "珠江新城"),
    ("深圳市", "南山区", "科技园"),
    ("深圳市", "福田区", "中心区"),
    ("杭州市", "西湖区", "文三路"),
    ("杭州市", "滨江区", "高新"),
    ("成都市", "高新区", "天府软件园"),
    ("武汉市", "洪山区", "光谷"),
    ("南京市", "建邺区", "河西"),
    ("苏州市", "工业园区", "金鸡湖"),
    ("西安市", "高新区", "科技路"),
    ("重庆市", "渝北区", "观音桥"),
]

# 姓名库
FIRST_NAMES = ["张", "李", "王", "赵", "刘", "陈", "杨", "黄", "周", "吴", "徐", "孙", "朱", "马", "胡", "郭", "林", "何", "高", "梁"]
LAST_NAMES = ["伟", "芳", "娜", "秀英", "敏", "静", "丽", "强", "磊", "军", "洋", "勇", "艳", "杰", "娟", "涛", "明", "超", "秀兰", "霞"]

# 公司名称前缀
COMPANY_PREFIX = [
    "北京", "上海", "广州", "深圳", "杭州", "成都", "武汉", "南京", "苏州", "西安",
    "腾讯", "阿里巴巴", "百度", "华为", "小米", "美团", "滴滴", "京东", "网易", "新浪",
    "华润", "万科", "恒大", "碧桂园", "中海", "绿地", "保利", "融创", "龙湖", "招商"
]

COMPANY_SUFFIX = [
    "科技有限公司", "贸易有限公司", "管理咨询有限公司", "文化传播有限公司",
    "建筑设计有限公司", "投资有限公司", "实业有限公司", "网络科技有限公司",
    "教育科技有限公司", "医疗科技有限公司", "汽车服务有限公司", "餐饮管理有限公司",
    "房地产经纪有限公司", "人力资源有限公司", "广告传媒有限公司", "环保科技有限公司"
]

def generate_random_name() -> str:
    """生成随机姓名"""
    return random.choice(FIRST_NAMES) + random.choice(LAST_NAMES)

def generate_company_name() -> str:
    """生成公司名称"""
    return random.choice(COMPANY_PREFIX) + random.choice(COMPANY_SUFFIX)

def generate_phone() -> str:
    """生成手机号"""
    prefixes = ['130', '131', '132', '133', '134', '135', '136', '137', '138', '139',
                '150', '151', '152', '153', '155', '156', '157', '158', '159',
                '180', '181', '182', '183', '184', '185', '186', '187', '188', '189']
    return random.choice(prefixes) + ''.join([str(random.randint(0, 9)) for _ in range(8)])

def generate_email(name: str) -> str:
    """生成邮箱"""
    domains = ['163.com', 'qq.com', '126.com', 'gmail.com', 'sina.com', 'sohu.com']
    name_pinyin = name.lower()
    return f"{name_pinyin}{random.randint(10, 99)}@{random.choice(domains)}"

def generate_address(region: tuple) -> str:
    """生成地址"""
    streets = ["建国路", "中山路", "人民大道", "解放街", "和平路", "文化路",
               "科技大道", "商务中心", "CBD", "中心广场", "金融街", "创业园"]
    numbers = random.randint(1, 999)
    return f"{region[0]}{region[1]}{region[2]}{random.choice(streets)}{numbers}号"

def generate_timestamp(days_ago: int = 0) -> int:
    """生成时间戳"""
    return int(datetime.datetime.now().timestamp()) - (days_ago * 24 * 3600) - random.randint(0, 86400)

def generate_customers(count: int) -> List[Dict]:
    """生成客户数据"""
    customers = []

    # 确保有各种状态的客户
    special_statuses = [1, 2, 3, 4, 5]
    for status in special_statuses:
        region = random.choice(REGIONS)
        customer = {
            'id': len(customers) + 1,
            'name': generate_company_name(),
            'customer_type': random.choice([1, 2]),  # 个人或企业
            'id_type': random.choice([1, 2, 3]),  # 证件类型
            'id_number': f"91{random.randint(100000000000000, 999999999999999)}",  # 统一社会信用代码或身份证
            'legal_person': generate_random_name(),
            'phone': generate_phone(),
            'province': region[0],
            'city': region[1],
            'district': region[2],
            'user_id': random.randint(1, 10),
            'department_id': random.randint(1, 5),
            'contact_name': generate_random_name(),
            'contact_phone': generate_phone(),
            'email': generate_email(generate_random_name()),
            'address': generate_address(region),
            'industry': random.choice(INDUSTRIES),
            'scale': random.choice(SCALES),
            'source': random.choice(SOURCES),
            'level': random.randint(1, 3),
            'status': status,
            'is_cooperation': 1 if status == 3 else 0,
            'member_status': random.choice([0, 1]),
            'customer_attribute': random.randint(1, 3),
            'member_type': random.choice([1, 2, 3, None]),
            'member_start_time': generate_timestamp(365) if random.choice([0, 1]) else None,
            'member_end_time': generate_timestamp(0) if random.choice([0, 1]) else None,
            'is_main_account': random.choice([0, 1, None]),
            'main_account_id': None,
            'official_customer_id': None,
            'cooperation_time': generate_timestamp(300) if status == 3 else 0,
            'remark': '测试数据',
            'create_time': generate_timestamp(365),
            'update_time': generate_timestamp(30),
            'delete_time': 0,
            'last_followup_time': generate_timestamp(random.randint(0, 30)) if status != 1 else 0,
            'followup_count': random.randint(0, 20),
            'opportunity_count': random.randint(0, 5),
            'order_count': random.randint(0, 5),
            'total_order_amount': round(random.uniform(0, 1000000), 2),
            'last_order_time': generate_timestamp(random.randint(0, 100)) if status == 3 else 0,
            'customer_tags': '',
            'important_level': random.randint(1, 3),
            'next_followup_plan': '跟进客户需求',
            'lost_reason': '测试数据' if status == 5 else ''
        }
        customers.append(customer)

    # 生成剩余客户
    for i in range(count - len(special_statuses)):
        region = random.choice(REGIONS)
        status = random.choices(
            population=[1, 2, 3, 4, 5],
            weights=[30, 35, 20, 10, 5]
        )[0]

        customer = {
            'id': len(customers) + 1,
            'name': generate_company_name(),
            'customer_type': random.choice([1, 2]),
            'id_type': random.choice([1, 2, 3]),
            'id_number': f"91{random.randint(100000000000000, 999999999999999)}",
            'legal_person': generate_random_name(),
            'phone': generate_phone(),
            'province': region[0],
            'city': region[1],
            'district': region[2],
            'user_id': random.randint(1, 10),
            'department_id': random.randint(1, 5),
            'contact_name': generate_random_name(),
            'contact_phone': generate_phone(),
            'email': generate_email(generate_random_name()),
            'address': generate_address(region),
            'industry': random.choice(INDUSTRIES),
            'scale': random.choice(SCALES),
            'source': random.choice(SOURCES),
            'level': random.randint(1, 3),
            'status': status,
            'is_cooperation': 1 if status == 3 else 0,
            'member_status': random.choice([0, 1]),
            'customer_attribute': random.randint(1, 3),
            'member_type': random.choice([1, 2, 3, None]),
            'member_start_time': generate_timestamp(365) if random.choice([0, 1]) else None,
            'member_end_time': generate_timestamp(0) if random.choice([0, 1]) else None,
            'is_main_account': random.choice([0, 1, None]),
            'main_account_id': None,
            'official_customer_id': None,
            'cooperation_time': generate_timestamp(300) if status == 3 else 0,
            'remark': '测试数据',
            'create_time': generate_timestamp(365),
            'update_time': generate_timestamp(30),
            'delete_time': 0,
            'last_followup_time': generate_timestamp(random.randint(0, 30)) if status != 1 else 0,
            'followup_count': random.randint(0, 20),
            'opportunity_count': random.randint(0, 5),
            'order_count': random.randint(0, 5),
            'total_order_amount': round(random.uniform(0, 1000000), 2),
            'last_order_time': generate_timestamp(random.randint(0, 100)) if status == 3 else 0,
            'customer_tags': '',
            'important_level': random.randint(1, 3),
            'next_followup_plan': '跟进客户需求',
            'lost_reason': '测试数据' if status == 5 else ''
        }
        customers.append(customer)

    return customers

def generate_contacts(customers: List[Dict]) -> List[Dict]:
    """生成联系人数据"""
    contacts = []
    contact_id = 1

    for customer in customers:
        num_contacts = random.randint(1, 2)
        for i in range(num_contacts):
            contact = {
                'id': contact_id,
                'customer_id': customer['id'],
                'name': generate_random_name(),
                'position': random.choice(["总经理", "销售总监", "市场经理", "采购经理", "项目经理", "董事长秘书", "办公室主任"]),
                'phone': generate_phone(),
                'wechat': f"wx_{random.randint(100000, 999999)}",
                'email': generate_email(generate_random_name()),
                'is_primary': True if i == 0 else False,
                'status': 1,
                'user_id': customer['user_id'],
                'create_time': generate_timestamp(365),
                'update_time': generate_timestamp(30),
                'delete_time': 0
            }
            contacts.append(contact)
            contact_id += 1

    return contacts

def generate_followups(customers: List[Dict]) -> List[Dict]:
    """生成跟进记录"""
    followups = []
    followup_id = 1

    for customer in customers:
        if customer['status'] == 1:
            num_followups = random.randint(0, 2)
        elif customer['status'] == 2:
            num_followups = random.randint(2, 8)
        elif customer['status'] == 3:
            num_followups = random.randint(3, 15)
        elif customer['status'] == 4:
            num_followups = random.randint(0, 1)
        else:
            num_followups = random.randint(1, 3)

        for i in range(num_followups):
            followup = {
                'id': followup_id,
                'customer_id': customer['id'],
                'followup_type': random.randint(1, 5),
                'followup_time': generate_timestamp(365),
                'content': random.choice([
                    "客户对产品很感兴趣，已发送详细资料",
                    "约定了下次沟通时间，详细介绍产品功能",
                    "客户提出了一些问题，已详细解答",
                    "客户需要与团队讨论后回复",
                    "客户认为价格偏高，协商中"
                ]),
                'followup_result': random.randint(1, 5),
                'next_followup_time': generate_timestamp(random.randint(3, 30)),
                'user_id': customer['user_id'],
                'create_time': generate_timestamp(365),
                'update_time': generate_timestamp(30),
                'delete_time': 0
            }
            followups.append(followup)
            followup_id += 1

    return followups

def generate_orders(customers: List[Dict]) -> List[Dict]:
    """生成订单数据"""
    orders = []
    order_id = 1

    for customer in customers:
        if customer['status'] in [2, 3]:
            if customer['status'] == 3:
                num_orders = random.randint(1, 5)
            else:
                num_orders = random.choices(
                    population=[0, 1, 2],
                    weights=[40, 50, 10]
                )[0]

            for i in range(num_orders):
                order = {
                    'id': order_id,
                    'customer_id': customer['id'],
                    'order_type': random.randint(1, 5),
                    'title': random.choice([
                        f"{random.choice(INDUSTRIES)}行业CRM系统定制",
                        f"{random.choice(INDUSTRIES)}信息化管理系统",
                        "企业数字化转型方案",
                        "客户管理系统维护服务",
                        "业务咨询与培训服务"
                    ]),
                    'description': "详细需求待进一步沟通确认",
                    'amount': random.randint(10000, 500000),
                    'status': random.choices(
                        population=[1, 2, 3, 4, 5],
                        weights=[10, 30, 40, 5, 15]
                    )[0],
                    'payment_status': random.randint(1, 4),
                    'contract_status': random.randint(1, 3),
                    'user_id': customer['user_id'],
                    'create_time': generate_timestamp(300),
                    'update_time': generate_timestamp(30),
                    'delete_time': 0
                }
                orders.append(order)
                order_id += 1

    return orders

def generate_sql(customers: List[Dict], contacts: List[Dict], followups: List[Dict], orders: List[Dict]) -> str:
    """生成SQL插入语句"""
    sql = "-- 营销CRM测试数据（适配实际表结构）\n"
    sql += "-- 生成时间: " + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "\n\n"

    # 插入客户数据
    sql += "-- 插入客户数据 (oa_crm_customer)\n"
    sql += "INSERT INTO `oa_crm_customer` ("
    sql += "`name`, `customer_type`, `id_type`, `id_number`, `legal_person`, `phone`, "
    sql += "`province`, `city`, `district`, `user_id`, `department_id`, "
    sql += "`contact_name`, `contact_phone`, `email`, `address`, `industry`, `scale`, `source`, "
    sql += "`level`, `status`, `is_cooperation`, `member_status`, `customer_attribute`, "
    sql += "`member_type`, `member_start_time`, `member_end_time`, `is_main_account`, "
    sql += "`main_account_id`, `official_customer_id`, `cooperation_time`, `remark`, "
    sql += "`create_time`, `update_time`, `delete_time`, `last_followup_time`, "
    sql += "`followup_count`, `opportunity_count`, `order_count`, `total_order_amount`, "
    sql += "`last_order_time`, `customer_tags`, `important_level`, `next_followup_plan`, `lost_reason`"
    sql += ") VALUES\n"

    for i, customer in enumerate(customers):
        def format_value(val):
            if val is None or val == 'None':
                return 'NULL'
            elif isinstance(val, str):
                # 转义单引号
                return f"'{val.replace(\"'\", \"\\'\")}'"
            else:
                return str(val)

        sql += f"("
        sql += f"{format_value(customer['name'])}, "
        sql += f"{format_value(customer['customer_type'])}, "
        sql += f"{format_value(customer['id_type'])}, "
        sql += f"{format_value(customer['id_number'])}, "
        sql += f"{format_value(customer['legal_person'])}, "
        sql += f"{format_value(customer['phone'])}, "
        sql += f"{format_value(customer['province'])}, "
        sql += f"{format_value(customer['city'])}, "
        sql += f"{format_value(customer['district'])}, "
        sql += f"{format_value(customer['user_id'])}, "
        sql += f"{format_value(customer['department_id'])}, "
        sql += f"{format_value(customer['contact_name'])}, "
        sql += f"{format_value(customer['contact_phone'])}, "
        sql += f"{format_value(customer['email'])}, "
        sql += f"{format_value(customer['address'])}, "
        sql += f"{format_value(customer['industry'])}, "
        sql += f"{format_value(customer['scale'])}, "
        sql += f"{format_value(customer['source'])}, "
        sql += f"{format_value(customer['level'])}, "
        sql += f"{format_value(customer['status'])}, "
        sql += f"{format_value(customer['is_cooperation'])}, "
        sql += f"{format_value(customer['member_status'])}, "
        sql += f"{format_value(customer['customer_attribute'])}, "
        sql += f"{format_value(customer['member_type'])}, "
        sql += f"{format_value(customer['member_start_time'])}, "
        sql += f"{format_value(customer['member_end_time'])}, "
        sql += f"{format_value(customer['is_main_account'])}, "
        sql += f"{format_value(customer['main_account_id'])}, "
        sql += f"{format_value(customer['official_customer_id'])}, "
        sql += f"{format_value(customer['cooperation_time'])}, "
        sql += f"{format_value(customer['remark'])}, "
        sql += f"{format_value(customer['create_time'])}, "
        sql += f"{format_value(customer['update_time'])}, "
        sql += f"{format_value(customer['delete_time'])}, "
        sql += f"{format_value(customer['last_followup_time'])}, "
        sql += f"{format_value(customer['followup_count'])}, "
        sql += f"{format_value(customer['opportunity_count'])}, "
        sql += f"{format_value(customer['order_count'])}, "
        sql += f"{format_value(customer['total_order_amount'])}, "
        sql += f"{format_value(customer['last_order_time'])}, "
        sql += f"{format_value(customer['customer_tags'])}, "
        sql += f"{format_value(customer['important_level'])}, "
        sql += f"{format_value(customer['next_followup_plan'])}, "
        sql += f"{format_value(customer['lost_reason'])}"
        sql += f")"

        if i < len(customers) - 1:
            sql += ","
        else:
            sql += ";"
        sql += "\n\n"

    # 插入联系人数据
    sql += "\n-- 插入联系人数据 (oa_crm_contact)\n"
    sql += "INSERT INTO `oa_crm_contact` ("
    sql += "`customer_id`, `name`, `position`, `phone`, `wechat`, `email`, "
    sql += "`is_primary`, `status`, `user_id`, `create_time`, `update_time`, `delete_time`"
    sql += ") VALUES\n"

    for i, contact in enumerate(contacts):
        def format_value(val):
            if val is None or val == 'None':
                return 'NULL'
            elif isinstance(val, str):
                return f"'{val.replace(\"'\", \"\\'\")}'"
            else:
                return str(val)

        sql += f"("
        sql += f"{format_value(contact['customer_id'])}, "
        sql += f"{format_value(contact['name'])}, "
        sql += f"{format_value(contact['position'])}, "
        sql += f"{format_value(contact['phone'])}, "
        sql += f"{format_value(contact['wechat'])}, "
        sql += f"{format_value(contact['email'])}, "
        sql += f"{format_value(contact['is_primary'])}, "
        sql += f"{format_value(contact['status'])}, "
        sql += f"{format_value(contact['user_id'])}, "
        sql += f"{format_value(contact['create_time'])}, "
        sql += f"{format_value(contact['update_time'])}, "
        sql += f"{format_value(contact['delete_time'])}"
        sql += f")"

        if i < len(contacts) - 1:
            sql += ","
        else:
            sql += ";"
        sql += "\n\n"

    # 插入跟进记录
    sql += "\n-- 插入跟进记录 (oa_crm_followup)\n"
    sql += "INSERT INTO `oa_crm_followup` ("
    sql += "`customer_id`, `followup_type`, `followup_time`, `content`, `followup_result`, "
    sql += "`next_followup_time`, `user_id`, `create_time`, `update_time`, `delete_time`"
    sql += ") VALUES\n"

    for i, followup in enumerate(followups):
        def format_value(val):
            if val is None or val == 'None':
                return 'NULL'
            elif isinstance(val, str):
                return f"'{val.replace(\"'\", \"\\'\")}'"
            else:
                return str(val)

        sql += f"("
        sql += f"{format_value(followup['customer_id'])}, "
        sql += f"{format_value(followup['followup_type'])}, "
        sql += f"{format_value(followup['followup_time'])}, "
        sql += f"{format_value(followup['content'])}, "
        sql += f"{format_value(followup['followup_result'])}, "
        sql += f"{format_value(followup['next_followup_time'])}, "
        sql += f"{format_value(followup['user_id'])}, "
        sql += f"{format_value(followup['create_time'])}, "
        sql += f"{format_value(followup['update_time'])}, "
        sql += f"{format_value(followup['delete_time'])}"
        sql += f")"

        if i < len(followups) - 1:
            sql += ","
        else:
            sql += ";"
        sql += "\n\n"

    # 插入订单数据
    if orders:
        sql += "\n-- 插入订单数据 (oa_crm_order)\n"
        sql += "INSERT INTO `oa_crm_order` ("
        sql += "`customer_id`, `order_type`, `title`, `description`, `amount`, "
        sql += "`status`, `payment_status`, `contract_status`, `user_id`, `create_time`, `update_time`, `delete_time`"
        sql += ") VALUES\n"

        for i, order in enumerate(orders):
            def format_value(val):
                if val is None or val == 'None':
                    return 'NULL'
                elif isinstance(val, str):
                    return f"'{val.replace(\"'\", \"\\'\")}'"
                else:
                    return str(val)

            sql += f"("
            sql += f"{format_value(order['customer_id'])}, "
            sql += f"{format_value(order['order_type'])}, "
            sql += f"{format_value(order['title'])}, "
            sql += f"{format_value(order['description'])}, "
            sql += f"{format_value(order['amount'])}, "
            sql += f"{format_value(order['status'])}, "
            sql += f"{format_value(order['payment_status'])}, "
            sql += f"{format_value(order['contract_status'])}, "
            sql += f"{format_value(order['user_id'])}, "
            sql += f"{format_value(order['create_time'])}, "
            sql += f"{format_value(order['update_time'])}, "
            sql += f"{format_value(order['delete_time'])}"
            sql += f")"

            if i < len(orders) - 1:
                sql += ","
            else:
                sql += ";"
            sql += "\n\n"

    return sql

def main():
    """主函数"""
    print("正在生成适配实际表结构的CRM测试数据...")
    print(f"目标客户数量: {CUSTOMER_COUNT}\n")

    random.seed(42)

    customers = generate_customers(CUSTOMER_COUNT)
    contacts = generate_contacts(customers)
    followups = generate_followups(customers)
    orders = generate_orders(customers)

    sql = generate_sql(customers, contacts, followups, orders)

    output_file = "crm_test_data_fixed.sql"
    with open(output_file, 'w', encoding='utf-8') as f:
        f.write(sql)

    print(f"SQL文件已生成: {output_file}")
    print(f"客户: {len(customers)}个")
    print(f"联系人: {len(contacts)}个")
    print(f"跟进记录: {len(followups)}条")
    print(f"订单: {len(orders)}个")

if __name__ == "__main__":
    main()
